<?php
/**商品管理*/
namespace App\Http\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Fitment extends Model
{
    /**
     * 家具列表
     * @param查询参数
    */
    protected static function fitment_list($param)
    {
        $filter = array();
        if(isset($param['fitment_brand']) && $param['fitment_brand'] !=''){ /**品牌搜索*/
            $filter[] = " fts.`fitments_brand_id` = {$param['fitment_brand']}";
        }
        if(isset($param['fitment_type_classify']) && $param['fitment_type_classify'] !=''){ /**家具分类[办公，家用，商用]*/
            $filter[] = " fts.`fitments_type` = {$param['fitment_type_classify']}";
        }
        if(isset($param['fitment_subcate_id']) && $param['fitment_subcate_id'] !=''){ /**家具分类[家具部件]*/
            $filter[] = " fts.`fitments_subcate_id` = {$param['fitment_subcate_id']}";
        }
        $where = ' fts.`fitments_isdel`=1 ';
        if ( count($filter) > 0 ){
            $where .=  ' AND '.implode(' AND ', $filter);
        }
        $currentpage = ($param['current_page']-1)*$param['per_page'];
        $limit = " LIMIT {$currentpage},{$param['per_page']} ";/**家具主表查询条件end*/
        /**家具详情查询条件start*/
        $price = array(
            '1'=>array('min'=>'100','max'=>'500'),
            '2'=>array('min'=>'500','max'=>'1000'),
            '3'=>array('min'=>'1000','max'=>'2000'),
        );
        $filterdt = array();
        /**家具名搜索*/
        if(isset($param['fitment_name']) && $param['fitment_name'] != '') {
            $filterdt[] = "(fts.`fitments_name` like '%{$param['fitment_name']}%')";
        }
        /**租赁价格查找*/
        if(isset($param['fitment_price']) && $param['fitment_price'] !=''){
            $filterdt[] = ' IF('.$param['fitment_rent'].' = 1,'.'dt.`details_fixprice`,dt.`details_nimbleprice`) '." >= {$price[$param['fitment_price']]['min']} AND ".'IF('.$param['fitment_rent'].' = 1,'.'dt.`details_fixprice`,dt.`details_nimbleprice`)'." <= {$price[$param['fitment_price']]['max']} ";
        }
        /**租赁价格区间查询*/
        if((isset($param['minprice']) && $param['minprice']!='') && (isset($param['maxprice']) && $param['maxprice']!='')){
            $filterdt[] = ' IF('.$param['fitment_rent'].' = 1,'.'dt.`details_fixprice`,dt.`details_nimbleprice`) '." >= {$param['minprice']} AND ".'IF('.$param['fitment_rent'].' = 1,'.'dt.`details_fixprice`,dt.`details_nimbleprice`)'." <= {$param['maxprice']} ";
        }
        $wheredt = '';
        if ( count($filterdt) > 0 ){
            $wheredt .= implode(' AND ', $filterdt);
            $wheredt = ' AND '.$wheredt;
        }
        $order = '';
        if($param['fitment_orderby']!='1') {
            if($param['fitment_orderby']=='3'){
                $order .= ' ORDER BY price DESC ';
            }else if($param['fitment_orderby']=='2'){
                $order .= ' ORDER BY price ASC ';
            }
            $order .= ' ,dt.`details_isdel` DESC ';
        }else{
            $order .= ' ORDER BY dt.`details_isdel` DESC ';
        };

        //家具列表查询修改
        $sql = 'SELECT * FROM mzj_fitment_details AS dt1  GROUP BY details_pid ';
        /**本地配置颜色图片路径*/
        $color_arr = COMMON::color_path();

        /**
         * 查询条件
         * @param['fitment_rent'] 租赁类型【1租满即送,2随租随还】
         */
        $sql_name = '(SELECT GROUP_CONCAT(DISTINCT(CASE `mzj_color`.`color_type`
                        WHEN 1 THEN '."'".$color_arr['1']['path']."'".' WHEN 2 THEN '."'".$color_arr['2']['path']."'".'                  
                        WHEN 3 THEN '."'".$color_arr['3']['path']."'".' WHEN 4 THEN '."'".$color_arr['4']['path']."'".'                  
                        WHEN 5 THEN '."'".$color_arr['5']['path']."'".' WHEN 6 THEN '."'".$color_arr['6']['path']."'".'                  
                        WHEN 7 THEN '."'".$color_arr['7']['path']."'".' WHEN 8 THEN '."'".$color_arr['8']['path']."'".'                  
                         ELSE '."'".$color_arr['9']['path']."'".' END)) AS color_types 
                    FROM `mzj_fitment_details` AS `mzj_dt1` 
                    LEFT JOIN `mzj_fitment_details` AS `mzj_dt2` ON `mzj_dt1`.`details_pid` = `mzj_dt2`.`details_pid` 
                    LEFT JOIN `mzj_fitment_color` AS `mzj_color` ON `mzj_color`.`color_details_id` = `mzj_dt2`.`details_id` 
                    WHERE `mzj_dt1`.`details_id` = dt.`details_id`) AS colors,CONCAT('."'".COMMON::erp_img_path()."'".',img.`fitment_details_imgMaster`) AS fitment_details_imgMaster,
                    dt.`details_name`,dt.`details_describe`,dt.`details_id`,
                    IF('.$param['fitment_rent'].' = 1,'.'dt.`details_fixprice`,dt.`details_nimbleprice`) AS price,
                    fts.`fitments_name`,fts.`fitments_describe`,fts.`fitments_brand_id`,fts.`fitments_type`,fts.`fitments_subcate_id`,fts.`fitments_id` ';

        $res = DB::select('SELECT '.$sql_name.'  FROM mzj_fitments AS fts 
                    JOIN ( '.$sql.' ORDER BY IF('.$param['fitment_rent'].' = 1,'.'dt1.`details_fixprice`,dt1.`details_nimbleprice`)) AS dt ON dt.`details_pid`=fts.`fitments_id` 
                    LEFT JOIN mzj_details_img AS img ON dt.details_id = img.`fitment_details_id` 
                    WHERE '.$where.$wheredt.$order.$limit);

        return $res;
    }

    /***
     * 家具详情
     * @param $param
     * @return string
     * @fit_dt_sizes 家具规矩详情数据
     * @color_types 家具颜色数据
     */
    protected static function fitment_detail($param)
    {
        $color_arr = COMMON::color_path();
        //查询数据字段
        $sql_name = ' fit.`fitments_id`,fit.`fitments_name`,fit.`fitments_describe`,REPLACE(fit.`fitments_contents`,'."'src=".'"/'."'".','."'src=".'"'."http://115.159.95.113:8080/'".') AS fitments_contents,
        fit_dt.`details_id`,fit_dt.`details_name`,fit_dt.`details_describe`,fit_dt.`details_bond`,fit_dt.`details_fixprice`,fit_dt.`details_nimbleprice`,
        IF('.$param['rent_type'].'=1,fit_dt.`details_nimbleprice`,fit_dt.`details_fixprice`) as price,
        fit_dt.`details_length`,fit_dt.`details_width`,fit_dt.`details_height`,fit_dt.`details_pid`,
        CONCAT(CONCAT('."'".COMMON::erp_img_path()."'".',img.`fitment_details_imgMaster`),'."','".',
        CONCAT('."'".COMMON::erp_img_path()."'".',img.`fitment_details_imgTwo`),'."','".',
        CONCAT('."'".COMMON::erp_img_path()."'".',img.`fitment_details_imgThree`),'."','".',
        CONCAT('."'".COMMON::erp_img_path()."'".',img.`fitment_details_imgFour`)) AS fitment_details_imgMaster,
        (CASE color.`color_type`
        WHEN 1 THEN '."'".$color_arr['1']['path']."'".' WHEN 2 THEN '."'".$color_arr['2']['path']."'".'                  
        WHEN 3 THEN '."'".$color_arr['3']['path']."'".' WHEN 4 THEN '."'".$color_arr['4']['path']."'".'                  
        WHEN 5 THEN '."'".$color_arr['5']['path']."'".' WHEN 6 THEN '."'".$color_arr['6']['path']."'".'                  
        WHEN 7 THEN '."'".$color_arr['7']['path']."'".' WHEN 8 THEN '."'".$color_arr['8']['path']."'".'                  
         ELSE '."'".$color_arr['9']['path']."'".' END) AS color_type
        ,color.`color_number`,color.`color_stock_number`,
        (SELECT GROUP_CONCAT(DISTINCT(CASE `mzj_color`.`color_type`
        WHEN 1 THEN '."'".$color_arr['1']['path']."'".' WHEN 2 THEN '."'".$color_arr['2']['path']."'".'                  
        WHEN 3 THEN '."'".$color_arr['3']['path']."'".' WHEN 4 THEN '."'".$color_arr['4']['path']."'".'                  
        WHEN 5 THEN '."'".$color_arr['5']['path']."'".' WHEN 6 THEN '."'".$color_arr['6']['path']."'".'                  
        WHEN 7 THEN '."'".$color_arr['7']['path']."'".' WHEN 8 THEN '."'".$color_arr['8']['path']."'".'                  
         ELSE '."'".$color_arr['9']['path']."'".' END)) AS color_types 
        FROM `mzj_fitment_details` AS `mzj_dt1` 
        LEFT JOIN `mzj_fitment_details` AS `mzj_dt2` ON `mzj_dt1`.`details_pid` = `mzj_dt2`.`details_pid` 
        LEFT JOIN `mzj_fitment_color` AS `mzj_color` ON `mzj_color`.`color_details_id` = `mzj_dt2`.`details_id` 
        WHERE `mzj_dt1`.`details_id` = fit_dt.`details_id`) AS color_types,
        (SELECT GROUP_CONCAT(CONCAT(fit_dt_size1.`details_id`,'."','".',fit_dt_size1.`details_length`,'."','".',fit_dt_size1.`details_width`,'."','".',fit_dt_size1.`details_height`)) 
        FROM mzj_fitment_details AS fit_dt_size1 
        LEFT JOIN mzj_fitment_details AS fit_dt_size2 ON fit_dt_size2.`details_pid`=fit_dt_size1.`details_pid` 
        WHERE fit_dt_size2.`details_id`=fit_dt.`details_id`) AS fit_dt_sizes';

        $res = DB::select('SELECT '.$sql_name.' FROM mzj_fitment_details AS fit_dt 
            INNER JOIN mzj_fitments AS fit ON fit.`fitments_id`=fit_dt.`details_pid` 
            LEFT JOIN mzj_details_img AS img ON img.`fitment_details_id`=fit_dt.`details_id` 
            LEFT JOIN mzj_fitment_color AS color ON color.`color_details_id`=fit_dt.`details_id` 
            WHERE fit_dt.`details_id`='.$param['id'].' AND fit_dt.`details_isdel`=1 AND fit.fitments_isdel=1');
        return $res;
    }

    /**
     * 家具分类查询(首页)
     * @per_page 必填参数 展示数量 *
     * @fitments_type 家具类型【1家用/2办公/3商用/14热门家具】
     * @rent_type 租赁方式【1随租随还，2租满即送】
     * @fitments_subcate_id 家具子分类id【桌子椅子凳子】
     * 【1=>[7=>办公桌，8=>办公椅，9=>文件柜，10=>配件]，2=>[31=>办公桌,32=>办公椅],3=>[33=>办公椅子,34=>办公桌]，14=>[35=>实木办公桌,36=>简约拼接桌,37=>职员办公椅]】
     */
    protected static function fitment_classify($param)
    {
        //查询字段
        $sql_name = 'fit.`fitments_name`,fit.`fitments_describe`,
                   fit_dt.details_id AS id,fit_dt.`details_pid` AS pid,fit_dt.`details_name`,fit_dt.`details_describe`,
                   fit_dt.`details_bond`,
                   IF('.$param['rent_type'].'=1,fit_dt.`details_nimbleprice`,fit_dt.`details_fixprice`) as price,
                   CONCAT('."'".COMMON::erp_img_path()."'".',img.`fitment_details_imgMaster`) AS img';
        $sql_where = '';
        $filter = array();
        if(isset($param['fitments_type']) && $param['fitments_type']!=''){
            $filter[] = ' fit.`fitments_type` = '.$param['fitments_type'];
        }
        if(isset($param['fitments_subcate_id']) && $param['fitments_subcate_id']!=''){
            $filter[] = ' fit.`fitments_subcate_id` = '.$param['fitments_subcate_id'];
        }
        if ( count($filter) > 0 ){
            $sql_where .= implode(' AND ', $filter);
            $sql_where = ' AND '.$sql_where;
        }
        $sql_limit = ' LIMIT '.$param['per_page'];
        $res = DB::select('SELECT '.$sql_name.' FROM mzj_fitments AS fit
        LEFT JOIN mzj_fitment_details AS fit_dt ON fit_dt.`details_pid`=fit.`fitments_id`
        LEFT JOIN mzj_details_img AS img ON img.`fitment_details_id`=fit_dt.`details_id`
        WHERE fit.`fitments_isdel`=1'.$sql_where.$sql_limit);

        return $res;
    }

    /**家具分类名*/
    protected static function cate()
    {
        //查询字段
        $sql_name = 'id,cate_name AS name';

        $res = DB::select('SELECT '.$sql_name.' FROM mzj_cate 
        WHERE cate_status=1');

        return $res;
    }

    /**家具栏目分类*/
    protected static function fitment_subcate_list($param)
    {
        $sql_name = '`id`,`subcate_pid`,`subcate_name`,CONCAT('."'".COMMON::erp_img_path()."'".',`subcate_img`) AS img';
        $res = DB::select(' SELECT '.$sql_name.' FROM mzj_subcate 
            WHERE `subcate_status`=1  AND `subcate_pid`='.$param['id']);
        return $res;
    }

}
